#include "xsql.h"
#include "qdebug.h"

XSQL::XSQL(QObject *parent): QObject{parent}
{

}

//打开数据库
bool XSQL::open(QSqlDatabase &data, QString path, QString connect)
{
    if(close(data))
        qDebug() << "去除旧连接"<< connect;

    data = QSqlDatabase::addDatabase("QSQLITE", connect);
    data.setDatabaseName(path);
    if (!data.open())
    {
        qDebug() << "连接" << connect << "数据库失败" << data.lastError();
        return false;
    }
    else
    {
        qDebug() << "成功连接" << connect << "数据库" ;
        return true;

    }
}

//关闭数据库
bool XSQL::close(QSqlDatabase &data)
{
    if(data.isOpen())
    {
        QString connection_name = data.connectionName();
        data.close();
        data = QSqlDatabase();
        data.removeDatabase(connection_name);
        return true;
    }
    else return false;
}

//创建表
//列表格式（表名，{表头[X] 类型},{表头[X] 类型}……）
//"KEY"默认为创建自增主键，表头id
bool XSQL::create_table(QSqlDatabase &data, QList<QString> list)
{
    QSqlQuery query(data);
    if(table_exists(query, list[0]))
    {
//        qDebug() << "创建表" + list[0] + "失败，已经存在这个表";
        return false;
    }

    if(list.count()<2)
    {
        qDebug() << "创建表" + list[0] + "失败，请至少包含主键列";
        return false;
    }
    QString content;
    for(int i=0; i<list.count(); i++)
    {
        if(i==0)
            content = content.append("CREATE TABLE " + list[i] + "(");
        else if(list[i] == "KEY")
            content = content.append("id INTEGER PRIMARY KEY AUTOINCREMENT, ");
        else if(i>0 && i<list.count()-1 && list[i] != "KEY")
            content = content.append(list[i] + ", ");
        else if(i == list.count()-1 && list[i] != "KEY")
            content = content.append(list[i] + ")");
        else
            qDebug() << "数组遍历错误，请检查拼写";
    }
    if(!query.exec(content))
    {
        qDebug() << "创建表" + list[0] + "失败，请检查列和类型是否拼写错误"<< query.lastError();
        return false;
    }
    else
    {
//        qDebug() << "创建表" + list[0] + "成功";
        return true;
    }
}

//重命名表
bool XSQL::rename_table(QSqlDatabase &data, QString old_name, QString new_name)
{
    QSqlQuery query(data);
    if(!table_exists(query, old_name))
    {
        qDebug() << "重命名表" + old_name + "失败，请检查是否存在这个表";
        return false;
    }
    if(table_exists(query, new_name))
    {
        qDebug() << "重命名表" + old_name + "失败，新表名已被占用";
        return false;
    }

    if(!query.exec("ALTER TABLE " + old_name + " RENAME TO " + new_name))
    {
        qDebug() << "重命名表" + old_name + "失败"<< query.lastError();
        return false;
    }
    else
    {
//        qDebug() << "重命名表" + old_name + "成功， 新表名：" + new_name;
        return true;
    }
}

//删除表
bool XSQL::delete_table(QSqlDatabase &data, QString table_name)
{
    QSqlQuery query(data);
    if(!table_exists(query, table_name))
    {
        qDebug() << "删除表" + table_name + "失败，请检查是否存在这个表";
        return false;
    }

    if(!query.exec("DROP TABLE IF EXISTS " + table_name))
    {
        qDebug() << "删除表" + table_name + "失败"<< query.lastError();
        return false;
    }
    else
    {
        qDebug() << "删除表" + table_name + "成功";
        return true;
    }
}

//添加新列
bool XSQL::add_column(QSqlDatabase &data, QString table_name, QString new_column)
{
    QSqlQuery query(data);
    if(!table_exists(query, table_name))
    {
        qDebug() << "表" + table_name + "中添加新列失败，请检查是否存在这个表";
        return false;
    }

    if(!query.exec("ALTER TABLE " + table_name + " ADD COLUMN " + new_column))
    {
        qDebug() << "表" + table_name + "中添加新列" + new_column + "失败"<< query.lastError();
        return false;
    }
    else
    {
//        qDebug() << "表" + table_name + "中添加新列" + new_column + "成功";
        return true;
    }
}

//插入行
//列表格式（表名，{表头[X]，数值},{表头[X]，数值}……）
bool XSQL::insert_row(QSqlDatabase &data, QList<QVariant> list)
{
    QSqlQuery query(data);
    if(!table_exists(query, list[0].toString()))
    {
        qDebug() << "表" + list[0].toString() + "中插入行失败，请检查是否存在这个表";
        return false;
    }

    if(list.count()<3)
    {
        qDebug() << "表" + list[0].toString() + "中插入行失败，请至少包含（表名，{表头[X]，数值})三组数值";
        return false;
    }
    QString content, columns, values;
    for(int i=1; i<list.count(); i++)
    {
        if(i%2!=0)
        {
            if(i==list.count()-2)
            {
                columns.append(list[i].toString());
                values.append(":" + list[i].toString());
            }
            else
            {
                columns.append(list[i].toString() + ", ");
                values.append(":" + list[i].toString() + ", ");
            }
        }
    }
    content = "INSERT INTO " + list[0].toString() + " (" + columns + ") VALUES (" + values + ")";
    query.prepare(content);
//    qDebug() << content;
    for(int i=1; i<list.count(); i++)
        if(i%2==0)
        {
            query.bindValue(":"+list[i-1].toString(), list[i]);
        }


    if(!query.exec())
    {
        qDebug() << "表" + list[0].toString() + "中插入行失败" << query.lastError();
        return false;
    }
    else
    {
//        qDebug() << "表" + list[0].toString() + "中插入行成功";
        return true;
    }

}

//更新行
//列表格式（表名，{表头[X]，数值},{表头[X]，数值}……，{表头[Y]，数值}）
bool XSQL::update_row(QSqlDatabase &data, QList<QVariant> list)
{
    QSqlQuery query(data);
    if(!table_exists(query, list[0].toString()))
    {
        qDebug() << "表" + list[0].toString() + "中更新行失败，请检查是否存在这个表";
        return false;
    }
    if(list.count()<5)
    {
        qDebug() << "表" + list[0].toString() + "中更新行失败，请至少包含（表名，{表头[X]，数值}，{表头[Y]，数值}）五组数值";
        return false;
    }
    QString content, condition, values;
    for(int i=1; i<list.count(); i++)
    {
        if(i%2!=0)
        {
            if(i==list.count()-4)
                values.append(list[i].toString() + " = :" + list[i].toString());
            else if(i==list.count()-2)
                condition.append(list[i].toString() + " = :" + list[i].toString());
            else
                values.append(list[i].toString() + " = :" + list[i].toString() + ", ");
        }
    }
    content = "UPDATE " + list[0].toString() + " SET " + values + " WHERE " + condition;
//    qDebug() << content;
    query.prepare(content);
    for(int i=1; i<list.count(); i++)
    {
        if(i%2==0)
        {
            query.bindValue(":"+list[i-1].toString(), list[i]);
        }
    }

    if(!query.exec())
    {
        qDebug() << "表" + list[0].toString() + "中更新行失败" << query.lastError();
        return false;
    }
    else
    {
//        qDebug() << "表" + list[0].toString() + "中更新行成功";
        return true;
    }
}
//删除行
//列表格式（表名，ID表头，ID）
bool XSQL::delete_row(QSqlDatabase &data, QList<QVariant> list)
{
    QString table_name = list[0].toString();
    QSqlQuery query(data);
    if(!table_exists(query, table_name))
    {
        qDebug() << "表" + table_name + "中删除行失败，请检查是否存在这个表";
        return false;
    }

    QString id_column = list[1].toString();
    int id = list[2].toInt();
    QString id_str = QString::number(id);
    if(!query.exec("DELETE FROM " +table_name + " WHERE "+ id_column +" = " + id_str))
    {
        qDebug()<< "删除行失败" << query.lastError();
        return false;
    }
    else
    {
//        qDebug()<<"已经删除ID为" << id_str << "的行";
        return true;
    }
}

//重命名ID
//列表格式（表名，ID表头，旧ID，新ID）
bool XSQL::rename_id(QSqlDatabase &data,  QList<QVariant> list)
{
    QString table_name = list[0].toString();
    QString id_column = list[1].toString();
    int old_id = list[2].toInt();
    int new_id = list[3].toInt();

    QSqlQuery query(data);
    if(!table_exists(query, table_name))
    {
        qDebug() << "表" + table_name + "中删除行失败，请检查是否存在这个表";
        return false;
    }
    QString old_id_str = QString::number(old_id);
    QString new_id_str = QString::number(new_id);
    if(!query.exec("UPDATE " + table_name + " SET "+ id_column +" = " + new_id_str + " WHERE "+ id_column +" = " + old_id_str))
    {
        qDebug()<< "重命名ID失败" << query.lastError();
        return false;
    }
    else
    {
//        qDebug()<<"重命名ID成功，ID从" + old_id_str + "变更为" + new_id_str;
        return true;
    }
}
//获取行
//列表格式（表名，{表头[X],表头[X],表头[X]……}，{ID表头，ID[Y]}）
//返回X和Y对应的值
QList<QVariant> XSQL::get_row(QSqlDatabase &data, QList<QVariant> list)
{
    QSqlQuery query(data);
    if(!table_exists(query, list[0].toString()))
    {
        qDebug() << "表" + list[0].toString() + "中行获取数据失败，请检查是否存在这个表";
    }
    if(list.count()<4)
    {
        qDebug() << "表" + list[0].toString() + "中行获取数据失败，请至少包含（表名，{表头[X]}, {ID表头，ID[Y]})四组数值";
    }

    QString column;
    QList<QVariant> list_return;
    QString id_column = list[list.count()-2].toString();

    QString id;
    QVariant id_var = list[list.count()-1];
    if(id_var.type() == QVariant::Int)
        id = QString::number(list[list.count()-1].toInt());
    else
        id = " '"+list[list.count()-1].toString()+"' ";

    for(int i=1; i<list.count()-2; i++)
    {
        if(i==list.count()-3)
            column.append(list[i].toString());
        else
            column.append(list[i].toString() + ", ");
    }
    QString content;
    content = "SELECT " + column + " FROM " + list[0].toString() + " WHERE "+ id_column +" = " + id;
//    qDebug() << content;
    if(!query.exec(content))
    {
        qDebug() << "获取指定获取行失败" << query.lastError();
    }
    else
    {
        while(query.next())
        {
            for(int i=0; i<list.count()-3; i++)
                list_return.append(query.value(i));
        }
//        qDebug() << "获取指定获取行成功";
    }

    return list_return;
}

//获取最大ID
int XSQL::get_max_id(QSqlDatabase &data, QString table_name)
{
    QSqlQuery query(data);
    if(!table_exists(query, table_name))
    {
        qDebug() << "表" + table_name + "中获取最大ID失败，请检查是否存在这个表";
    }

    int max_id = 0;
    query.prepare("SELECT max(id) FROM " + table_name);
    if (!query.exec())
    {
        qDebug() << "获取最大id失败" << query.lastError();
    }
    else
    {
        while (query.next())
        {
            max_id = query.value(0).toInt();
        }
//        qDebug() << "获取最大id成功 " << max_id;
    }
    return max_id;
}
//获取最小ID
int XSQL::get_min_id(QSqlDatabase &data, QString table_name)
{
    QSqlQuery query(data);
    if(!table_exists(query, table_name))
    {
        qDebug() << "表" + table_name + "中获取最小ID失败，请检查是否存在这个表";
    }

    int min_id = 0;
    query.prepare("SELECT min(id) FROM " + table_name);
    if (!query.exec())
    {
        qDebug() << "获取最小id失败" << query.lastError();
    }
    else
    {
        while (query.next())
        {
            min_id = query.value(0).toInt();
        }
//        qDebug() << "获取最小id成功 " << min_id;
    }
    return min_id;
}

bool XSQL::table_exists(QSqlQuery &query, QString table_name)
{
    query.exec("SELECT name FROM sqlite_master WHERE type='table' AND name='"+table_name+"'");
    if(query.next())
    {
       return true;
    }
    else
    {
        return false;
    }
}
